-- =============================================
-- This procedures gets all the documents that need to be published.
--
-- Modofications
-- 06/16/2003     E.Tatsui
-- =============================================


CREATE  PROCEDURE [dbo].[amsp_CMGetContentToPublish]
  @InPublishRequestDetailID numeric,
  @InDeleteFlag char(1) = 'N'
AS
BEGIN
  
  DECLARE
    @ContentID numeric,
    @NavMenuID numeric,
    @FileCount integer,
    @LinkCount integer,
    @HTMLContentID numeric,
    @HTMLTitle varchar(255),
    @ContentName varchar(255),
    @SourceLocation varchar(255),
    @PublishLocation varchar(255),
    @WorkflowStatusCode char(1),
    @NavContentGroupInd char(1),
    @MicrositeID numeric,
    @PreFuseURL varchar(255),
    @PostFuseURL varchar(255),
    @NavMenuPreFuseURL varchar(255),
    @NavMenuPostFuseURL varchar(255),
    @FilePath varchar(255),
    @URLSafeName varchar(255),
    @DefaultContentID numeric,
    @PreviousContentID numeric,
    @ShowInTemplateFlag char(1),
    @FileTypeCode varchar(4),
    @FileName varchar(255),
    @Description varchar(255),
    @DirectListComboInd char(1),
    @WebsiteRootURL varchar(255),
    @SecureWebsiteRootURL varchar(255),
    @TemplatePath varchar(255),
    @SectionName varchar(255),
    @MembersOnlyFlag char(1),
    @PrevMembersOnlyFlag char(1),
    @SourceRootLocation varchar(255),
    @PublishDirectory varchar(255),
    @Header varchar(500),
    @Footer varchar(500),
    @Keywords varchar(255),
    @DefaultGenerated bit,
    @PublishRegenerateInd char(1),
    @DefaultFileName varchar(255),
    @DefaultContentFlag char(1),
    @OldPublishedDefaultFileName varchar(255),
    @NewPublishedDefaultFileName varchar(255),
    @NavMenuWorkflowStatus char(1),
    @PublishedDirectory varchar(255),
    @LinkURL varchar(510),
    @SecureFlag char(1),
    @Root varchar(255),
    @PreviousWebsiteKey uniqueidentifier,
    @WebsiteKey uniqueidentifier

  SELECT @ContentID = c.ContentID,
         @NavMenuID = c.NavMenuID,
         @ContentName = c.Name,
         @PreFuseURL = c.PreFuseURL,
         @PostFuseURL = c.PostFuseURL,
         @URLSafeName = c.URLSafeName,
         @MembersOnlyFlag = IsNull(c.MembersOnlyFlag,'N'),
         @PrevMembersOnlyFlag = IsNull(pv.MembersOnlyFlag,'N'),
         @WorkflowStatusCode = c.WorkflowStatusCode,
         @PreviousContentID = c.PreviousContentID,
         @ShowInTemplateFlag = c.ShowInTemplateFlag,
         @Keywords = c.Keywords,
         @SecureFlag = c.SecureFlag,
         @LinkCount = (SELECT Count(*) FROM Content_Link  WITH (NOLOCK) WHERE ContentID = c.ContentID),
         @FileCount = (SELECT Count(*) FROM Content_File  WITH (NOLOCK) WHERE ContentID = c.ContentID),
         @HTMLContentID = ch.ContentID,
         @HTMLTitle = ch.Title,
         @DefaultContentID = n.ContentID,
         @NavContentGroupInd = n.NavContentGroupInd,
         @FilePath = n.FilePath,
         @DirectListComboInd = n.DirectListComboInd,
         @TemplatePath = n.TemplatePath,
         @SectionName = n.Name,
         @WebsiteRootURL = w.WebsiteRootURL,
         @SecureWebsiteRootURL = w.SecureWebsiteRootURL,
         @PublishRegenerateInd = p.PublishRegenerateInd,
         @DefaultFileName = w.DefaultFileName,
         @OldPublishedDefaultFileName = n.PublishedDefaultFileName,
         @NavMenuWorkflowStatus = n.WorkflowStatusCode,
         @PublishedDirectory = n.PublishedDirectory,
         @PublishDirectory = Replace(CASE WHEN n.NavContentGroupInd = 'N' THEN IsNull(w.NavPublishDirectory,'')
                                          ELSE IsNull(w.ContentFolderPublishDirectory,'') END
                                     + n.FilePath,'\','/'),
         @PreviousWebsiteKey = n.PreviousWebsiteKey,
         @WebsiteKey = n.WebsiteKey
    FROM Nav_Menu n WITH (NOLOCK), Content c WITH (NOLOCK) LEFT OUTER JOIN Content_HTML ch WITH (NOLOCK)

      ON c.ContentID = ch.ContentID
    LEFT OUTER JOIN Content pv WITH (NOLOCK)
      ON c.PreviousContentID = pv.ContentID, Publish_Request_Detail p WITH (NOLOCK), Website w WITH (NOLOCK)
   WHERE n.NavMenuID = c.NavMenuID
     AND c.ContentID = p.ContentID
     AND p.WebsiteKey = w.WebsiteKey
     AND p.PublishRequestDetailID = @InPublishRequestDetailID


  -- Is this a default content for the nav item?
  IF (@PreviousContentID = @DefaultContentID OR @ContentID = @DefaultContentID)
      AND @NavContentGroupInd = 'N'
    SET @DefaultContentFlag = 'Y'

  SET @DefaultGenerated = 0
  SET @Header = ''
  SET @Footer = '<!--- Generated by eContentManager - Advanced Solutions International, Inc. --->' + Char(13) + Char(10)

  -- Approved or already published contents.
  IF @InDeleteFlag = 'N' AND (@WorkflowStatusCode = 'A' OR @WorkflowStatusCode = 'P') BEGIN

    IF (@FileCount > 0 OR @LinkCount > 0 OR @HTMLContentID IS NOT NULL) BEGIN
      IF @MicrositeID > 0 BEGIN
        SET @SourceLocation = NULL
      
      END -- Ends special rule for microsite contents.
  
      ELSE BEGIN -- Non-microsite contents
        IF @FileCount > 0 AND @PublishRegenerateInd = 'P' BEGIN
          -- Add all the uploaded files to copy
          INSERT INTO #PublishContent
                      (PublishRequestDetailID,
                       SourceLocation,
                       PublishLocation,
                       PublishDirectory,
                       ContentFileID,
                       UseProtectedPath)
          SELECT @InPublishRequestDetailID,
                 SourceLocation,
                 @PublishDirectory + FileName,
                 @PublishDirectory,
                 ContentFileID,
                 @MembersOnlyFlag
            FROM Content_File WITH (NOLOCK)
           WHERE ContentID = @ContentID
        END

        -- If this is a default content and it's a list or combo mode,
        -- create a default document.
        IF @MembersOnlyFlag = 'N' AND @DirectListComboInd IN ('L','C') AND @DefaultContentFlag = 'Y'
          BEGIN
          EXEC amsp_CMGetFuseURL @NavMenuID, NULL, @NavMenuPreFuseURL OUTPUT, @NavMenuPostFuseURL OUTPUT, 1

          SET @SourceLocation = @WebsiteRootURL + @NavMenuPreFuseURL + '&FuseFlag=1'

          -- If DefaultFileName is specified, use it.
          IF @DefaultFileName IS NOT NULL BEGIN
            SET @PublishLocation = @PublishDirectory + @DefaultFileName
            SET @NewPublishedDefaultFileName = @DefaultFileName
          END
          ELSE BEGIN
            SET @PublishLocation = @PublishDirectory + 'Default' + Convert(varchar(20),@NavMenuID) + '.htm'
            SET @NewPublishedDefaultFileName = 'Default' + Convert(varchar(20),@NavMenuID) + '.htm'
          END
          SET @DefaultGenerated = 1

          INSERT INTO #PublishContent
                      (PublishRequestDetailID,
                       SourceLocation,
                       PublishLocation,
                       PublishDirectory,
                       Header,
                       Footer,
                       UseProtectedPath,
                       DefaultContentFlag)
          VALUES (@InPublishRequestDetailID,
                  @SourceLocation,
                  @PublishLocation,
                  @PublishDirectory,
                  @Header,
                  @Footer,
                  @MembersOnlyFlag,
                  'Y')
        END

        -- If there is only a single file/link or this content is Member's only file,
        -- and we're using default page,
        -- let's generate a file which simply redirects to the file.
        -- This file should always be in non-member's only directory (accessible through Web)
        IF @DefaultContentFlag = 'Y' AND @DefaultFileName IS NOT NULL
           AND @DefaultGenerated = 0
           AND ((@FileCount = 1 AND @LinkCount = 0 AND @HTMLContentID IS NULL)
                OR (@LinkCount = 1 AND @FileCount = 0 AND @HTMLContentID IS NULL)
                OR  @MembersOnlyFlag = 'Y')BEGIN

          SET @NewPublishedDefaultFileName = @DefaultFileName
          
          IF UPPER(LEFT(@PostFuseURL,4)) = 'HTTP'
            SET @LinkURL = @PostFuseURL
          ELSE IF @SecureFlag = 'Y'
            SET @LinkURL = @SecureWebsiteRootURL + @PostFuseURL
          ELSE
            SET @LinkURL = @WebsiteRootURL + @PostFuseURL

          SET @DefaultGenerated = 1
          INSERT INTO #PublishContent
                      (PublishRequestDetailID,
                       SourceLocation,
                       PublishLocation,
                       PublishDirectory,
                       Header,
                       Footer,
                       UseProtectedPath,
                       RedirectFileFlag,
                       DefaultContentFlag)
          VALUES (@InPublishRequestDetailID,
                  @LinkURL,
                  @PublishDirectory + @DefaultFileName,
                  @PublishDirectory,
                  @Header,
                  @Footer,
                  'N',
                  'Y',
                  'N')
        END

        -- This content has mix of links, html and files, create a main page to go to.
        IF @MembersOnlyFlag = 'N' AND
            (@FileCount > 1 OR @LinkCount > 1
                 OR (@HTMLContentID IS NOT NULL AND (@FileCount > 0 OR @LinkCount > 0))
                 OR (@FileCount > 0 AND @LinkCount > 0)) BEGIN
          SET @SourceLocation = @WebsiteRootURL + @PreFuseURL + '&FuseFlag=1'
          -- If DefaultFileName is specified, use it.
          IF @DefaultFileName IS NOT NULL AND @DefaultContentFlag = 'Y' AND @DefaultGenerated = 0 BEGIN
            SET @PublishLocation = @PublishDirectory + @DefaultFileName
            SET @NewPublishedDefaultFileName = @DefaultFileName
            SET @DefaultGenerated = 1
          END
          ELSE BEGIN
            SET @PublishLocation = @PublishDirectory + @URLSafeName + '.htm'
            IF @DefaultContentFlag = 'Y'
              SET @NewPublishedDefaultFileName = @URLSafeName + '.htm'
          END
          INSERT INTO #PublishContent
                      (PublishRequestDetailID,
                       SourceLocation,
                       PublishLocation,
                       PublishDirectory,
                       Header,
                       Footer,
                       UseProtectedPath,
                       DefaultContentFlag)
          VALUES (@InPublishRequestDetailID,
                  @SourceLocation,
                  @PublishLocation,
                  @PublishDirectory,
                  @Header,
                  @Footer,
                  @MembersOnlyFlag,
                  'Y')
        END
  
        -- If this content record has only Content_HTML record, set source loc & publish loc.
        IF @HTMLContentID IS NOT NULL BEGIN
          DECLARE
            @TempDefaultFlag char(1)

          IF @MembersOnlyFlag = 'Y' BEGIN
      DECLARE @MembersOnlyURL varchar(255)
            SET @MembersOnlyURL = Replace(UPPER(@PreFuseURL),UPPER('/MembersOnly.cfm'),'/CM/HTMLDisplay.cfm')
            SET @MembersOnlyURL = Replace(UPPER(@MembersOnlyURL),UPPER('/MembersOnly.aspx'),'/HTMLDisplay.aspx')
            SET @SourceLocation = @WebsiteRootURL + @MembersOnlyURL + '&FuseFlag=1'
          END
          ELSE
            SET @SourceLocation = @WebsiteRootURL + @PreFuseURL + '&FuseFlag=1'
          IF @DefaultFileName IS NOT NULL AND @DefaultContentFlag = 'Y' AND @DefaultGenerated = 0 BEGIN
            SET @PublishLocation = @PublishDirectory + @DefaultFileName
            SET @NewPublishedDefaultFileName = @DefaultFileName
            SET @DefaultGenerated = 1
            SET @TempDefaultFlag = 'Y'
          END
          ELSE BEGIN
            SET @PublishLocation = @PublishDirectory + @URLSafeName + '.htm'
            IF @DefaultContentFlag = 'Y' AND @DefaultGenerated = 0
              SET @NewPublishedDefaultFileName = @URLSafeName + '.htm'
            SET @TempDefaultFlag = 'N'
          END

          INSERT INTO #PublishContent
                      (PublishRequestDetailID,
                       SourceLocation,
                       PublishLocation,
                       PublishDirectory,
                       Header,
                       Footer,
                       HTMLContentID,
                       UseProtectedPath,
                       DefaultContentFlag)
          VALUES (@InPublishRequestDetailID,
                  @SourceLocation,
                  @PublishLocation,
                  @PublishDirectory,
                  @Header,
                  @Footer,
                  @HTMLContentID,
                  @MembersOnlyFlag,
                  @TempDefaultFlag)
        END    
      END -- Ends non-microsite Contents
    END -- @FileCount > 0 OR @LinkCount > 0 OR @HTMLContentID IS NOT NULL

    -- Finally, add files to be deleted.
    IF @PublishRegenerateInd = 'P' BEGIN
      -- If default file name has changed, add the old default file to the delete list
      IF @DefaultContentFlag = 'Y' AND @OldPublishedDefaultFileName IS NOT NULL
        AND (IsNull(@NewPublishedDefaultFileName,'') != @OldPublishedDefaultFileName) BEGIN
        INSERT INTO #PublishContent
                    (PublishRequestDetailID,
                     PublishLocation,
                     RemoveFlag,
                     UseProtectedPath)
        VALUES (@InPublishRequestDetailID,
                @PublishedDirectory + @OldPublishedDefaultFileName,
                'Y',
                @MembersOnlyFlag)

        IF @MembersOnlyFlag = 'Y'
          INSERT INTO #PublishContent
                      (PublishRequestDetailID,
                       PublishLocation,
                       RemoveFlag,
                       UseProtectedPath)
          VALUES (@InPublishRequestDetailID,
                  @PublishedDirectory + @OldPublishedDefaultFileName,
                  'Y',
                  'Y')
      END

      -- Save the new DefaultFileName
      IF @DefaultContentFlag = 'Y' AND IsNull(@NewPublishedDefaultFileName,'') != IsNull(@OldPublishedDefaultFileName,'')
        UPDATE Nav_Menu
           SET PublishedDefaultFileName = @NewPublishedDefaultFileName
         WHERE NavMenuID = @NavMenuID

      INSERT INTO #PublishContent
                  (PublishRequestDetailID,
                   PublishLocation,
                   RemoveFlag,
                   UseProtectedPath)
      SELECT @InPublishRequestDetailID,
             a.PublishLocation,
             'Y',
             @MembersOnlyFlag
        FROM Content a WITH (NOLOCK) LEFT OUTER JOIN #PublishContent x
          ON a.PublishLocation = x.PublishLocation
       WHERE a.ContentID IN (@PreviousContentID, @ContentID)
         AND a.PublishLocation IS NOT NULL
         AND (@PreviousWebsiteKey IS NULL OR a.PublishLocation LIKE @PublishDirectory + '%')
         AND x.PublishLocation IS NULL

      INSERT INTO #PublishContent
                  (PublishRequestDetailID,
                   PublishLocation,
                   RemoveFlag,
                   UseProtectedPath)
      SELECT @InPublishRequestDetailID,
             a.PublishLocation,
             'Y',
             @MembersOnlyFlag
        FROM Content_File a WITH (NOLOCK) LEFT OUTER JOIN #PublishContent x
          ON a.PublishLocation = x.PublishLocation
       WHERE a.ContentID IN (@PreviousContentID, @ContentID)
         AND a.PublishLocation IS NOT NULL
         AND x.PublishLocation IS NULL
         AND (@PreviousWebsiteKey IS NULL OR a.PublishLocation LIKE @PublishDirectory + '%')

      -- If FilePath has been changed since the last publish,
      -- add the folder to the delete list.
      IF @DefaultContentFlag = 'Y' AND @PublishedDirectory IS NOT NULL AND @PublishedDirectory <> @PublishDirectory
        INSERT INTO #PublishContent
                    (PublishRequestDetailID,
                     PublishDirectory,
                     DeleteFolderFlag,
                     UseProtectedPath)
        VALUES (@InPublishRequestDetailID,
                @PublishedDirectory,
                'Y',
                @MembersOnlyFlag)

      -- If Member's only flag has been changed, remove files from old location.
      IF @WorkflowStatusCode = 'A' AND IsNull(@PrevMembersOnlyFlag,'N') <> IsNull(@MembersOnlyFlag,'N')
        INSERT INTO #PublishContent
                    (PublishRequestDetailID,
                     PublishLocation,
                     RemoveFlag,
                     UseProtectedPath)
        SELECT @InPublishRequestDetailID,
               a.PublishLocation,
               'Y',
               a.MembersOnlyFlag
          FROM Content a WITH (NOLOCK)
         WHERE a.ContentID = @PreviousContentID
        UNION
        SELECT @InPublishRequestDetailID,
               a.PublishLocation,
               'Y',
               IsNull(@PrevMembersOnlyFlag,'N')
          FROM Content_File a WITH (NOLOCK)
         WHERE a.ContentID = @PreviousContentID

    END --- PublishRegenrateInd = 'P'
  END -- Approved or already published contents
  -- If delete flag is set, delete the entire directory.
  ELSE IF @InDeleteFlag = 'Y' AND @PreviousWebsiteKey IS NOT NULL BEGIN
    DECLARE
      @PreviousDirectory varchar(255)

      SELECT @PreviousDirectory = Replace(CASE WHEN @NavContentGroupInd = 'N' THEN IsNull(w.NavPublishDirectory,'')
                                          ELSE IsNull(w.ContentFolderPublishDirectory,'') END
                                     + @FilePath,'\','/')
        FROM Website w WITH (NOLOCK)
       WHERE WebsiteKey = @PreviousWebsiteKey

      INSERT INTO #PublishContent
                  (PublishRequestDetailID,
                   PublishDirectory,
                   DeleteFolderContentFlag,
                   UseProtectedPath)
      VALUES (@InPublishRequestDetailID,
              @PreviousDirectory,
              'Y',
              @MembersOnlyFlag)

      IF @MembersOnlyFlag = 'Y' AND @OldPublishedDefaultFileName IS NOT NULL
        INSERT INTO #PublishContent
                    (PublishRequestDetailID,
                     PublishDirectory,
                     DeleteFolderContentFlag,
                     UseProtectedPath)
        VALUES (@InPublishRequestDetailID,
                @PreviousDirectory,
                'Y',
                'N')
  END
  ELSE IF (@WorkflowStatusCode = 'D' OR @WorkflowStatusCode = 'R') BEGIN -- Content to Delete
    -- Delete all the files from Content, Content_HTML and Content_File.
    INSERT INTO #PublishContent
                (PublishRequestDetailID,
                 PublishLocation,
                 RemoveFlag,
                 UseProtectedPath)
    SELECT @InPublishRequestDetailID,
           PublishLocation,
           'Y',
           a.MembersOnlyFlag
      FROM Content a WITH (NOLOCK)
     WHERE ContentID = @ContentID
       AND PublishLocation IS NOT NULL

    INSERT INTO #PublishContent
                (PublishRequestDetailID,
                 PublishLocation,
                 RemoveFlag,
                 UseProtectedPath)
    SELECT @InPublishRequestDetailID,
           PublishLocation,
           'Y',
           @MembersOnlyFlag
      FROM Content_HTML a WITH (NOLOCK)
     WHERE ContentID = @ContentID
       AND PublishLocation IS NOT NULL

    INSERT INTO #PublishContent
                (PublishRequestDetailID,
                 PublishLocation,
                 RemoveFlag,
                 UseProtectedPath)
    SELECT @InPublishRequestDetailID,
           PublishLocation,
           'Y',
           @MembersOnlyFlag
      FROM Content_File a WITH (NOLOCK)
     WHERE ContentID = @ContentID
       AND PublishLocation IS NOT NULL

    -- For Member's only flag, remove default file from regular path as well.
    IF @MembersOnlyFlag = 'Y'
      INSERT INTO #PublishContent
                  (PublishRequestDetailID,
                   PublishLocation,
                   RemoveFlag,
                   UseProtectedPath)
      SELECT @InPublishRequestDetailID,
             PublishLocation,
             'Y',
             'N'
        FROM Content a WITH (NOLOCK)
       WHERE ContentID = @ContentID
         AND PublishLocation IS NOT NULL

    --If Nav_Menu record is marked for deletion, add the folder to delete list.
    IF @NavMenuWorkflowStatus = 'D' BEGIN
        INSERT INTO #PublishContent
                    (PublishRequestDetailID,
                     PublishDirectory,
                     DeleteFolderFlag)
        VALUES (@InPublishRequestDetailID,
                @PublishedDirectory,
                'Y')

       -- If member's only, we need to remove member's only directory as well.
       IF @MembersOnlyFlag = 'Y'
        INSERT INTO #PublishContent
                    (PublishRequestDetailID,
                     PublishDirectory,
                     DeleteFolderFlag,
                     UseProtectedPath)
        VALUES (@InPublishRequestDetailID,
                @PublishedDirectory,
                'Y',
                'Y')
    END
  END
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetContentToPublish] TO [IMIS]
GO